﻿GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_VendorVisitXMissionStructurePole_SelectForVisitToAssetGrid')
	DROP PROCEDURE up_VendorVisitXMissionStructurePole_SelectForVisitToAssetGrid
GO

CREATE PROCEDURE [dbo].[up_VendorVisitXMissionStructurePole_SelectForVisitToAssetGrid]
(
	@projectFk int
	,@PageSize int = 100
	,@PageNumber int = 1
	,@feets int = 20
)
AS

--Local variables
DECLARE @StartRowNumber int
DECLARE @EndRowNumber int
DECLARE @UtilityId int

SET @StartRowNumber = ((@PageNumber - 1) * @PageSize) + 1
SET @EndRowNumber = @PageNumber * @PageSize

SELECT 
	@UtilityId = UtilityFk
FROM
	tb_vendor_projects
WHERE
	Vendor_ProjectId = @projectFk

SELECT 
	Data.Vendor_Visit_XMissionStructures_PoleId InspVisitId
	,ISNULL(ISNULL(
			CAST(Data.Utility_Asset_XMissionStructures_PoleFk as varchar), 
			(
				--Gets closest
				SELECT TOP 1
					CAST(assets.Utility_Asset_XMissionStructures_PoleId as varchar) Utility_Asset_XMissionStructures_PoleFk
				FROM 
					tb_Utility_Asset_XMissionStructures_Poles assets
				INNER JOIN tb_Utility_Asset_XMissionStructures st
					ON st.Utility_Asset_XMissionStructureId = assets.Utility_Asset_XMissionStructureFK
				WHERE
					st.UtilityFk = @UtilityId
					AND dbo.fn_DistanceBetween(Data.Latitude, Data.Longitude, assets.Latitude, assets.Longitude) < @feets
			)
		), '<<new>>') AssetId
	,TotalCount
FROM 
	(
		SELECT 
			ROW_NUMBER() OVER (ORDER BY poles.Vendor_Visit_XMissionStructures_PoleId) AS RowNumber
			, COUNT(poles.Vendor_Visit_XMissionStructures_PoleId) OVER (PARTITION BY NULL) AS TotalCount
			, poles.Vendor_Visit_XMissionStructures_PoleId
			, poles.Utility_Asset_XMissionStructures_PoleFk
			, poles.Latitude
			, poles.Longitude
		FROM 
			tb_Vendor_Visit_XMissionStructures_Poles poles
		INNER JOIN tb_Vendor_Visit_XMissionStructures st
			ON poles.Vendor_Visit_XMissionStructureFk = st.Vendor_Visit_XMissionStructureId
		WHERE
			st.Vendor_ProjectFK = @projectFk
	) DATA
	LEFT JOIN tb_Utility_Asset_XMissionStructures_Poles assets
		ON DATA.Utility_Asset_XMissionStructures_PoleFk = assets.Utility_Asset_XMissionStructures_PoleId
WHERE 
	RowNumber BETWEEN @StartRowNumber AND @EndRowNumber  